<!DOCTYPE HTML>
<html>
<link href="main.css" rel="stylesheet" type="text/css">

<title>SoftPlus</title><body>
<table border="0" align="center">
  <tr>
    <td width="150px"></td>
    <td width="1000px"><img src="banner-01n.gif" width="1000" height="90" alt="Softplus"></td>
  </tr>
  <tr>
    <td valign="top"><?php
      require("navandset.php");
    ?></td>
<td valign="top">

<h1>Sales report for the last week</h1>

<?php
//Connect
$condet = GetGlobalConnectionOptions();
$con = mysql_connect($condet["server"],$condet["username"],$condet["password"]);
if (!$con)
{
	die("Could not connect: " . mysql_error());
}
//Select DB
mysql_select_db($condet["database"], $con);

//Code
//Query to find the sales totals
$qry = 
"SELECT SUM(sold) AS sold, SUM(gross) AS gross, SUM(profit) AS profit
FROM (
SELECT item, name, SUM(sold) AS sold, SUM(sold)*price AS gross, SUM(sold)*price-SUM(sold)*wholesaleprice AS profit
FROM (
SELECT tblorders.orderid, tblorders.creationdate, tblorderitems.item, tblitems.name, tblitems.price, tblitems.wholesaleprice, tblorderitems.quantity AS sold
FROM tblorders, tblorderitems, tblitems
WHERE tblorders.orderid = tblorderitems.orderid
AND tblorderitems.item = tblitems.item
AND tblorders.creationdate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()) tbl1
GROUP BY item) tbl2;";
$totals = mysql_fetch_array(mysql_query($qry));

//Generate query that will be displayed in the table
$qry = "
SELECT tblitems.item, tblitems.name, tbl2.sold, tbl2.gross, tbl2.profit
FROM tblitems LEFT JOIN (
SELECT item, name, SUM(sold) AS sold, SUM(sold)*price AS gross, SUM(sold)*price-SUM(sold)*wholesaleprice AS profit
FROM (
SELECT tblorders.orderid, tblorders.creationdate, tblorderitems.item, tblitems.name, tblitems.price, tblitems.wholesaleprice, tblorderitems.quantity AS sold
FROM tblorders, tblorderitems, tblitems
WHERE tblorders.orderid = tblorderitems.orderid
AND tblorderitems.item = tblitems.item
AND tblorders.creationdate BETWEEN DATE_SUB(NOW(), INTERVAL 7 DAY) AND NOW()) tbl1
GROUP BY item) tbl2
ON tblitems.item = tbl2.item ";
$page = 1;
if($_GET)
{
	switch($_GET["operation"])
	{
		case "Search":
			$page = 1;
			break;
		case "Apply Filters":
			$page = 1;
			break;
		case "Go To Page":
			$page = $_GET["page"];
			break;
		case "Previous":
			if($page > 1)
			{
				$page -= 1;
			};
			break;
		case "Next":
			$page += 1;
			break;
	};
	//Apply search filter
	$qry .= "WHERE tblitems.name LIKE '%".$_GET["search"]."%' ";
	//Apply sortings
	if($_GET["sort"])
	{
		$qry .= "ORDER BY ";
		switch($_GET["sort"])
		{
			case "nameasc":
				$qry .= "tblitems.name asc ";
				break;
			case "namedesc":
				$qry .= "tblitems.name desc ";
				break;
			case "soldasc":
				$qry .= "tbl2.sold asc ";
				break;
			case "solddesc":
				$qry .= "tbl2.sold desc ";
				break;
			case "grossasc":
				$qry .= "tbl2.gross asc ";
				break;
			case "grossdesc":
				$qry .= "tbl2.gross desc ";
				break;
			case "profitasc":
				$qry .= "tbl2.profit asc ";
				break;
			case "profitdesc":
				$qry .= "tbl2.profit desc ";
				break;
		};
	};
};
$qry .= ";";
$items = mysql_query($qry);
$numitems = mysql_num_rows($items);
$numpages = 1;
$firstitem = 0;
$enditem = 0;
if($numitems > 0)
{
	$numpages = ceil($numitems/10);
	if($page > $numpages)
	{
		$page = $numpages;
	};
	$firstitem = ($page-1)*10;
	$enditem = $page*10-1;
	if($enditem > $numitems)
	{
		$enditem = $numitems-1;
	}
};
?>

<table border="1">
	<tr>
		<td width="100px"><h2>Units Sold:<h2></td>
		<td width="100px"><h2><?php echo $totals["sold"]; ?><h2></td>
		<td width="100px"><h2>Gross:<h2></td>
		<td width="100px"><h2>$<?php echo $totals["gross"]; ?><h2></td>
		<td width="100px"><h2>Profit:<h2></td>
		<td width="100px"><h2>$<?php echo $totals["profit"]; ?><h2></td>
	</tr>
</table>

<form action="rptsales.php" method="get">
<h2>Search Name:<input type="text" name="search" size="100" maxlength="100" <?php echo "value='".$_GET["search"]."' "; ?>/><input type="submit" name="operation" value="Search"></h2>
<table border="1">
	<tr><td valign="top">
		<table width="840px" border="0" cellpadding="0">
		<tr>
		<th><h3>Name <input type="radio" name="sort" value="nameasc" <?php if($_GET["sort"]=="nameasc") echo "checked='checked'" ?> /> &and; <input type="radio" name="sort" value="namedesc" <?php if($_GET["sort"]=="namedesc") echo "checked='checked'" ?> /> &or; </h3></th>
		<th><h3>Units Sold <input type="radio" name="sort" value="soldasc" <?php if($_GET["sort"]=="soldasc") echo "checked='checked'" ?> /> &and; <input type="radio" name="sort" value="solddesc" <?php if($_GET["sort"]=="solddesc") echo "checked='checked'" ?> /> &or; </h3></th>
		<th><h3>Gross <input type="radio" name="sort" value="grossasc" <?php if($_GET["sort"]=="grossasc") echo "checked='checked'" ?> /> &and; <input type="radio" name="sort" value="grossdesc" <?php if($_GET["sort"]=="grossdesc") echo "checked='checked'" ?> /> &or; </h3></th>
		<th><h3>Profit <input type="radio" name="sort" value="profitasc" <?php if($_GET["sort"]=="profiteasc") echo "checked='checked'" ?> /> &and; <input type="radio" name="sort" value="profitdesc" <?php if($_GET["sort"]=="profitdesc") echo "checked='checked'" ?> /> &or; </h3></th>
		</tr>
		<?php
			mysql_data_seek($items, $firstitem);
			for($i = $firstitem; $i <= $enditem; $i++)
			{
				$item = mysql_fetch_array($items);
				echo "<tr>";
				echo "<td align='middle'><a href='rptproductinfo.php?item=".$item["item"]."'><h5>".$item["name"]."</h5></a></td>";
				echo "<td align='middle'><h5>";
				if($item["sold"]==NULL)
				{
					echo "0";
				}
				else
				{
					echo $item["sold"];
				};
				echo "</h5></td>";
				echo "<td align='right'><h5>$";
				if($item["gross"]==NULL)
				{
					echo "0";
				}
				else
				{
					echo $item["gross"];
				};
				echo "</h5></td>";
				echo "<td align='right'><h5>$";
				if($item["profit"]==NULL)
				{
					echo "0";
				}
				else
				{
					echo $item["profit"];
				};
				echo "</h5></td>";
				echo "</tr>";
			};
		?>
		</table>
	</td></tr>
	<tr><td>
		<table border="0" width="840px">
		<tr><td align="left">
		<input type="submit" name="operation" value="Apply Sort">
		</td><td></td><td align="right"><h4>
		<input type="submit" name="operation" value="Go To Page">
		<select name="page">
		<?php
			for($i = 1; $i <= $numpages; $i++)
			{
				if($i == $page)
				{
					echo "<option value='".$i."' selected='selected'>".$i."</option>";
				}
					else
				{
					echo "<option value='".$i."'>".$i."</option>";
				};
			};
		?>
		</select>
		of <?php echo $numpages; ?> 
		<input type="submit" name="operation" value="Previous">
		<input type="submit" name="operation" value="Next">
		</h4></td></tr>
		</table>
	</td></tr>
</table></form>
<?php
//Disconnect
mysql_close($con);
?>

</td>
  </tr>
</table>
</body>

</html>
